from typing import List
from pymysql.cursors import Cursor
from utils.common_sydr import *



def deleMd5TableByUserId(mysqlConnect,userId,tableName:str):
    cursor, db = mysqlConnect
    deleSql = "DELETE from " +tableName+ md5valueHeader(str(userId)) + " where user_id={}".format(userId)
    cursor.execute(deleSql)
    db.commit()


# 删除用户的考试和做题记录
def deleUserExam(mysqlConnect, topicDataGenerator,redisConnect,sessionList:List[Session]):
    cursor, db=mysqlConnect
    userIds=[]
    for i in sessionList:
        userIds.append(i.headers.get("userId"))
    userIds=tuple(userIds)
    if len(userIds)>1:
        deleSql = "DELETE from tms_exam3 where user_id in {}".format(userIds)
    elif len(userIds)==1:
        deleSql = "DELETE from tms_exam3 where user_id={}".format(userIds[0])

    cursor.execute(deleSql)
    deleSql = "delete from tms_otopic_record where id in {}".format(tuple(topicDataGenerator["tms_otopic.id"]))
    cursor.execute(deleSql)

    deleSql = "delete from tms_stopic_record where stopic_id in {}".format(tuple(topicDataGenerator["tms_stopic.id"]))
    cursor.execute(deleSql)
    for i in userIds:
        deleSql = "DELETE from tms_mock_user where user_id={}".format(i)
        cursor.execute(deleSql)
        deleMd5TableByUserId(mysqlConnect,i,"tms_exam3_result")
        deleMd5TableByUserId(mysqlConnect, i, "tms_stopic_question_user_record")
        deleMd5TableByUserId(mysqlConnect, i, "tms_stopic_user_record")
        deleMd5TableByUserId(mysqlConnect, i, "tms_user_otopic_record")
        redisConnect.delete("ums:global:setting:%s"%(i))
        redisConnect.delete("tms:wrongOtopic:3:4:%s" % (i))

    db.commit()
    for i in topicDataGenerator["tms_otopic.id"]:
        redisConnect.hdel("tms:otopicRecordDoneNum",str(i))
        redisConnect.hdel("tms:otopicRecordErrorNum", str(i))

# mysql根据字段名获取索引
def getMysqlIndex(mysqlCon:Connection,tableName,prop):
    L=getPorpsOfMysql(mysqlCon,tableName)
    for i in range(len(L)):
        if L[i]==prop:
            return i

# 获取mysql表的表属性list
def getPorpsOfMysql(mysqlCon:Connection,tableName)->List:
    mysqlcursor=mysqlCon.cursor()
    mysqlcursor.execute("desc %s" % (tableName))
    desc = mysqlcursor.fetchall()
    L=[]
    for i in desc:
        L.append(i[0])
    return L


def OnlyDeleExam(userId, mysqlConnect, topicDataGenerator):
    cursor, db = mysqlConnect
    deleSql = "DELETE from tms_exam3 where user_id={} and paper_id={}".format(userId,topicDataGenerator["tms_paper_normal.id"][0])
    cursor.execute(deleSql)
    db.commit()




# sqlite根据字段名获取索引
def getSqliteIndex(sqliteCon:Connection,tableName,prop)->int:
    L=getPorpsOfSqlite(sqliteCon,tableName)
    for i in range(len(L)):
        if L[i]==prop:
            return i

# 获取sqlite表的表属性list
def getPorpsOfSqlite(sqliteCon:Connection,tableName)->List:
    cursorSqlite = sqliteCon.cursor()
    sql = 'PRAGMA table_info("%s");' % (tableName)
    cursorSqlite.execute(sql)
    res1 = cursorSqlite.fetchall()
    res = []
    for i in res1:
        res.append(i[1])
    return res


def fetchMaxId(tableName,cursor:Cursor):
    selectSql="select id from "+tableName+" ORDER BY id DESC LIMIT 1;"
    cursor.execute(selectSql)
    maxId=cursor.fetchone()
    if maxId!=None:
        maxId=maxId[0]
    else:
        maxId=0
    return maxId
